/* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   Module Name          : com.yaodian100.ec.product.dao.impl.SpecificationInventoryDaoImpl
   Module Description   :

   Date Created      : 2009/11/24
   Original Author   : ellen.li
   Team              : yaodian100
   ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
   MODIFICATION HISTORY
   ------------------------------------------------------------------------------
   Date Modified       Modified by       Comments
   ------------------------------------------------------------------------------
   ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
package com.yaodian100.ec.product.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;

import com.yaodian100.core.common.dao.impl.HibernateObjectDaoImpl;
import com.yaodian100.core.common.exception.CoreException;
import com.yaodian100.ec.product.dao.SpecificationInventoryDao;
import com.yaodian100.ec.product.entity.Product;
import com.yaodian100.ec.product.entity.SpecificationInventory;

/**
 * @author ellen.li
 * 
 */
public class SpecificationInventoryDaoImpl extends HibernateObjectDaoImpl<SpecificationInventory> implements
		SpecificationInventoryDao {
	public SpecificationInventoryDaoImpl() throws ClassNotFoundException {
		super(SpecificationInventory.class.getName());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.yaodian100.ec.product.dao.SpecificationInventoryDao#getSpecificationInventorySum(com.yaodian100.ec.product
	 * .entity.SpecificationInventory)
	 */
	@Override
	public Map<String, String> getSpecificationInventorySum(List products) throws CoreException {
		Map<String, String> resultMap = new HashMap<String, String>();
		if (!products.isEmpty()) {
			StringBuffer sb = new StringBuffer();
			List<Product> aproducts = products;
			sb.append("and inv.product_id in(");
			for (Product product : aproducts) {
				sb.append("'");
				sb.append(product.getProductId());
				sb.append("'");
				sb.append(",");
			}

			String modifysql = sb.toString();
			modifysql = modifysql.substring(0, modifysql.length() - 1);
			modifysql = modifysql + ")";

			StringBuffer sql = new StringBuffer();
			sql.append("select sp.product_id,inv.spec_id,sp.specification_name,");
			sql.append("case when (inv.available_inventory + inv.cancel_inventory - inv.sold_inventory) < 0 then 0 ");
			sql.append("else (inv.available_inventory + inv.cancel_inventory - inv.sold_inventory) end as qty ");
			sql.append(",inv.available_inventory, inv.sold_inventory,inv.cancel_inventory  ");
			// sql.append("from ec_specification_inventory as inv,ec_specification as sp ");
			sql.append("from ec_specification_inventory inv,ec_specification sp,ec_shelf_main sh,ec_product_main pm ");
			sql.append("where inv.spec_id = sp.specification_id and inv.product_id = sp.product_id ");
			sql.append("and sysdate between sh.start_date and sh.end_date and sh.shelf_status= 'SHF500' ");
			sql.append("and sh.product_id = pm.product_id and pm.is_delete != 'Y' and sp.is_delete != 'Y' and pm.product_id = sp.product_id ");
			sql.append(modifysql);
			sql
					.append("group by sp.specification_name,sp.product_id,inv.spec_id,inv.available_inventory, inv.sold_inventory,inv.cancel_inventory  ");
			// logger.info("xxxsql.toString() :{}", sql.toString());
			SQLQuery query = getSession().createSQLQuery(sql.toString());
			String tmpName = "";
			String tmpQty = "";
			List lists = query.list();
			String spName;
			if (!lists.isEmpty()) {
				for (Object obj : lists) {
					Object[] objArray = (Object[]) obj;
					if (null == objArray[2]) {
						spName = "";
					} else {
						spName = objArray[2].toString();
					}
					if (resultMap.containsKey(objArray[0].toString())) {
						tmpName = resultMap.get(objArray[0].toString());
						tmpQty = resultMap.get(objArray[3].toString());
						if (null != tmpQty) {
							resultMap.put(objArray[0].toString(), "<BR/>规格名称：" + spName + ",可售数(" + objArray[3].toString() + ")=库存数("
									+ objArray[4].toString() + ")-已售数(" + objArray[5].toString() + ")+取消数(" + objArray[6].toString()
									+ ")" + tmpName + ",可購數量:" + tmpQty);
						} else {
							resultMap.put(objArray[0].toString(), "<BR/>规格名称：" + spName + ",可售数(" + objArray[3].toString() + ")=库存数("
									+ objArray[4].toString() + ")-已售数(" + objArray[5].toString() + ")+取消数(" + objArray[6].toString()
									+ ")" + tmpName);
						}
					} else {
						resultMap.put(objArray[0].toString(), "<BR/>规格名称：" + spName + ",可售数(" + objArray[3].toString() + ")=库存数("
								+ objArray[4].toString() + ")-已售数(" + objArray[5].toString() + ")+取消数(" + objArray[6].toString() + ")");
					}
				}
			}
			// logger.info("lists.size():", lists.size());
		}
		return resultMap;
	}

}
